<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>InnoDB行级锁深度解析 | 技术小馆</title>
    <link rel="stylesheet" href="https://cdn.staticfile.org/tailwindcss/2.2.19/tailwind.min.css">
    <link rel="stylesheet" href="https://cdn.staticfile.org/font-awesome/6.4.0/css/all.min.css">
    <link href="https://fonts.googleapis.com/css2?family=Noto+Serif+SC:wght@400;500;600;700&family=Noto+Sans+SC:wght@300;400;500;700&display=swap" rel="stylesheet">
    <script src="https://cdn.jsdelivr.net/npm/mermaid@latest/dist/mermaid.min.js"></script>
    <style>
        body {
            font-family: 'Noto Sans SC', Tahoma, Arial, Roboto, "Droid Sans", "Helvetica Neue", "Droid Sans Fallback", "Heiti SC", "Hiragino Sans GB", Simsun, sans-serif;
            color: #333;
            line-height: 1.6;
            background-color: #f9fafb;
        }
        h1, h2, h3, h4 {
            font-family: 'Noto Serif SC', serif;
            font-weight: 600;
            color: #111;
        }
        .hero {
            background: linear-gradient(135deg, #1e3a8a 0%, #2563eb 100%);
        }
        .code-block {
            background-color: #2d3748;
            border-radius: 0.5rem;
            padding: 1.5rem;
            position: relative;
        }
        .code-block::before {
            content: '';
            position: absolute;
            top: 0;
            left: 0;
            right: 0;
            height: 2.5rem;
            background-color: rgba(255,255,255,0.1);
            border-top-left-radius: 0.5rem;
            border-top-right-radius: 0.5rem;
        }
        .code-block::after {
            content: '';
            position: absolute;
            top: 0.75rem;
            left: 1rem;
            width: 0.75rem;
            height: 0.75rem;
            background-color: #f56565;
            border-radius: 50%;
            box-shadow: 1.25rem 0 0 #f6e05e, 2.5rem 0 0 #48bb78;
        }
        .card {
            transition: all 0.3s ease;
            box-shadow: 0 4px 6px -1px rgba(0, 0, 0, 0.1), 0 2px 4px -1px rgba(0, 0, 0, 0.06);
        }
        .card:hover {
            transform: translateY(-4px);
            box-shadow: 0 10px 15px -3px rgba(0, 0, 0, 0.1), 0 4px 6px -2px rgba(0, 0, 0, 0.05);
        }
        .table-container {
            overflow-x: auto;
        }
        table {
            min-width: 100%;
            border-collapse: separate;
            border-spacing: 0;
        }
        th, td {
            padding: 0.75rem 1rem;
            text-align: left;
            border-bottom: 1px solid #e2e8f0;
        }
        th {
            background-color: #f7fafc;
            font-weight: 600;
        }
        tr:hover td {
            background-color: #f8fafc;
        }
        .divider {
            height: 1px;
            background: linear-gradient(90deg, rgba(0,0,0,0) 0%, rgba(66,153,225,0.5) 50%, rgba(0,0,0,0) 100%);
            margin: 2rem 0;
        }
        .tip-box {
            border-left: 4px solid #4299e1;
            background-color: #ebf8ff;
            padding: 1rem;
            border-radius: 0 0.5rem 0.5rem 0;
        }
        .warning-box {
            border-left: 4px solid #f6ad55;
            background-color: #fffaf0;
            padding: 1rem;
            border-radius: 0 0.5rem 0.5rem 0;
        }
        .danger-box {
            border-left: 4px solid #f56565;
            background-color: #fff5f5;
            padding: 1rem;
            border-radius: 0 0.5rem 0.5rem 0;
        }
        .success-box {
            border-left: 4px solid #48bb78;
            background-color: #f0fff4;
            padding: 1rem;
            border-radius: 0 0.5rem 0.5rem 0;
        }
        .drop-cap:first-letter {
            float: left;
            font-size: 4rem;
            line-height: 0.65;
            margin: 0.1em 0.3rem 0 0;
            color: #4299e1;
            font-weight: bold;
        }
    </style>
</head>
<body>
    <!-- Hero Section -->
    <section class="hero text-white py-20 px-4 md:px-0">
        <div class="container mx-auto max-w-4xl">
            <div class="flex flex-col md:flex-row items-center">
                <div class="md:w-1/2 mb-10 md:mb-0">
                    <h1 class="text-4xl md:text-5xl font-bold mb-6">InnoDB行级锁深度解析</h1>
                    <p class="text-xl opacity-90 mb-8">从停车场到数据库：解锁高性能并发控制的秘密</p>
                    <div class="flex items-center">
                        <span class="inline-flex items-center px-4 py-2 rounded-full bg-blue-700 text-sm font-medium mr-3">
                            <i class="fas fa-database mr-2"></i> MySQL
                        </span>
                        <span class="inline-flex items-center px-4 py-2 rounded-full bg-blue-600 text-sm font-medium">
                            <i class="fas fa-lock mr-2"></i> 并发控制
                        </span>
                    </div>
                </div>
                <div class="md:w-1/2">
                    <img src="https://cdn.nlark.com/yuque/0/2025/png/21449790/1743053934325-663b70fa-b4eb-4dc0-b067-435e5f50f642.png" alt="行级锁示意图" class="rounded-lg shadow-2xl border-4 border-white">
                </div>
            </div>
        </div>
    </section>

    <!-- Main Content -->
    <main class="container mx-auto max-w-4xl px-4 py-12">
        <!-- Section 1 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">1</span>
                什么是行级锁？
            </h2>
            <div class="prose max-w-none">
                <p class="drop-cap">想象你开车进了一个智能停车场——每个车位都有独立的电子锁。InnoDB的行级锁就像这些智能车位锁，只有被占用的位置才会上锁。当你的Java程序执行<code class="bg-gray-100 px-2 py-1 rounded">UPDATE user SET name='老王' WHERE id=5</code>时，InnoDB只会锁住id=5这一行数据。</p>
                
                <div class="my-8">
                    <img src="https://cdn.nlark.com/yuque/0/2025/png/21449790/1743053934325-663b70fa-b4eb-4dc0-b067-435e5f50f642.png" alt="行级锁示意图" class="rounded-lg shadow-md w-full">
                </div>
                
                <div class="grid grid-cols-1 md:grid-cols-3 gap-6 my-8">
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-blue-600 text-2xl mb-3">
                            <i class="fas fa-bullseye"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">细粒度</h3>
                        <p class="text-gray-600">只锁定需要修改的数据</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-blue-600 text-2xl mb-3">
                            <i class="fas fa-robot"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">自动管理</h3>
                        <p class="text-gray-600">根据事务自动加锁/解锁</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-blue-600 text-2xl mb-3">
                            <i class="fas fa-exclamation-triangle"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">冲突检测</h3>
                        <p class="text-gray-600">写锁会阻塞其他写锁</p>
                    </div>
                </div>
                
                <div class="warning-box my-8">
                    <div class="flex items-start">
                        <div class="flex-shrink-0 text-yellow-500 mt-1 mr-3">
                            <i class="fas fa-exclamation-circle"></i>
                        </div>
                        <div>
                            <p class="font-semibold">现实往往更骨感</p>
                            <p>上周我们生产环境就出现一个事故：批量更新10万条数据时，整个表都被锁住了！通过<code class="bg-yellow-100 px-1 py-0.5 rounded">SHOW ENGINE INNODB STATUS</code>查看锁信息，发现竟然出现了<strong>间隙锁</strong>（Gap Lock）。原来当where条件使用范围查询时，InnoDB会给不存在的记录区间也上锁，就像停车场把空车位也锁了不让停。</p>
                        </div>
                    </div>
                </div>
                
                <div class="code-block text-gray-200 my-8">
                    <pre><code>-- 事务1
BEGIN;
SELECT * FROM orders WHERE amount > 100 FOR UPDATE;

-- 事务2（会被阻塞）
INSERT INTO orders (amount) VALUES (150);</code></pre>
                </div>
            </div>
        </section>
        
        <div class="divider"></div>
        
        <!-- Section 2 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">2</span>
                比Java的synchronized复杂10倍
            </h2>
            <div class="prose max-w-none">
                <h3 class="text-2xl font-semibold mb-4 flex items-center">
                    <span class="bg-blue-100 text-blue-800 rounded-full w-8 h-8 flex items-center justify-center text-sm mr-3">2.1</span>
                    记录锁（Record Lock）
                </h3>
                <p>就是最基本的行锁，相当于Java里的<code class="bg-gray-100 px-2 py-1 rounded">synchronized</code>方法。但不同之处在于，记录锁可以有<strong>共享锁（S锁）</strong>和<strong>排他锁（X锁）</strong>：</p>
                
                <div class="code-block text-gray-200 my-6">
                    <pre><code>SELECT * FROM table WHERE id=1 LOCK IN SHARE MODE; -- S锁
SELECT * FROM table WHERE id=1 FOR UPDATE;         -- X锁</code></pre>
                </div>
                
                <h3 class="text-2xl font-semibold mb-4 flex items-center">
                    <span class="bg-blue-100 text-blue-800 rounded-full w-8 h-8 flex items-center justify-center text-sm mr-3">2.2</span>
                    间隙锁（Gap Lock）
                </h3>
                <p>这个锁专门锁两个现有记录之间的空隙。比如现有id为1、3、5的记录，执行<code class="bg-gray-100 px-2 py-1 rounded">WHERE id > 3 AND id <5</code>时会锁住(3,5)这个区间，防止其他事务插入id=4的数据。这就像在停车场3号位和5号位之间拉上警戒线。</p>
                
                <h3 class="text-2xl font-semibold mb-4 flex items-center">
                    <span class="bg-blue-100 text-blue-800 rounded-full w-8 h-8 flex items-center justify-center text-sm mr-3">2.3</span>
                    临键锁（Next-Key Lock）
                </h3>
                <p>记录锁+间隙锁的组合，相当于把当前记录和前面的间隙一起锁住。这是InnoDB默认的锁模式，也是很多死锁问题的罪魁祸首。</p>
                
                <div class="my-8">
                    <div class="mermaid">
                        graph LR
                            A[记录锁] --> C[临键锁]
                            B[间隙锁] --> C
                    </div>
                </div>
            </div>
        </section>
        
        <div class="divider"></div>
        
        <!-- Section 3 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">3</span>
                数据库版的锁
            </h2>
            <div class="prose max-w-none">
                <p>锁的兼容性就像多人协作的规则表：</p>
                
                <div class="table-container my-6">
                    <table class="w-full bg-white rounded-lg overflow-hidden">
                        <thead>
                            <tr>
                                <th></th>
                                <th>X锁</th>
                                <th>S锁</th>
                                <th>间隙锁</th>
                            </tr>
                        </thead>
                        <tbody>
                            <tr>
                                <td>X锁</td>
                                <td class="text-red-500">❌</td>
                                <td class="text-red-500">❌</td>
                                <td class="text-red-500">❌</td>
                            </tr>
                            <tr>
                                <td>S锁</td>
                                <td class="text-red-500">❌</td>
                                <td class="text-green-500">✅</td>
                                <td class="text-green-500">✅</td>
                            </tr>
                            <tr>
                                <td>间隙锁</td>
                                <td class="text-red-500">❌</td>
                                <td class="text-green-500">✅</td>
                                <td class="text-green-500">✅</td>
                            </tr>
                        </tbody>
                    </table>
                </div>
                
                <div class="tip-box my-8">
                    <div class="flex items-start">
                        <div class="flex-shrink-0 text-blue-500 mt-1 mr-3">
                            <i class="fas fa-lightbulb"></i>
                        </div>
                        <div>
                            <p class="font-semibold">案例分析</p>
                            <p>最近排查的一个死锁案例：两个事务同时执行<code class="bg-blue-100 px-1 py-0.5 rounded">UPDATE table SET col=val WHERE index_col BETWEEN 10 AND 20</code>，由于临键锁的范围重叠，导致互相等待。解决方案是改用<code class="bg-blue-100 px-1 py-0.5 rounded">IN()</code>列举具体值，缩小锁定范围。</p>
                        </div>
                    </div>
                </div>
            </div>
        </section>
        
        <div class="divider"></div>
        
        <!-- Section 4 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">4</span>
                如何解决死锁
            </h2>
            <div class="prose max-w-none">
                <h3 class="text-2xl font-semibold mb-4">死锁产生的四个必要条件：</h3>
                <ol class="list-decimal pl-6 space-y-2">
                    <li>互斥条件</li>
                    <li>请求与保持条件</li>
                    <li>不剥夺条件</li>
                    <li>循环等待条件</li>
                </ol>
                
                <p class="mt-6">通过<code class="bg-gray-100 px-2 py-1 rounded">SHOW ENGINE INNODB STATUS</code>查看最新死锁信息：</p>
                
                <div class="code-block text-gray-200 my-6">
                    <pre><code>LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 114514, OS thread handle 0x7f8d44064700, query id 666 127.0.0.1 root updating
UPDATE account SET balance = balance - 100 WHERE user_id = 1

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 114515, OS thread handle 0x7f8d44065700, query id 667 127.0.0.1 root updating
UPDATE account SET balance = balance + 100 WHERE user_id = 2</code></pre>
                </div>
                
                <h3 class="text-2xl font-semibold mb-4">分析步骤：</h3>
                <ol class="list-decimal pl-6 space-y-2">
                    <li>确认死锁事务的SQL语句</li>
                    <li>查看锁等待关系图</li>
                    <li>检查索引使用情况</li>
                    <li>重现并验证解决方案</li>
                </ol>
                
                <div class="my-8">
                    <div class="mermaid">
                        graph TD
                            A[发现死锁] --> B[分析死锁日志]
                            B --> C[确认SQL语句]
                            C --> D[检查索引]
                            D --> E[重现问题]
                            E --> F[验证解决方案]
                    </div>
                </div>
            </div>
        </section>
        
        <div class="divider"></div>
        
        <!-- Section 5 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">5</span>
                如何优化
            </h2>
            <div class="prose max-w-none">
                <h3 class="text-2xl font-semibold mb-4 flex items-center">
                    <span class="bg-blue-100 text-blue-800 rounded-full w-8 h-8 flex items-center justify-center text-sm mr-3">5.1</span>
                    索引优化
                </h3>
                <ul class="list-disc pl-6 space-y-2">
                    <li>所有where条件字段必须走索引</li>
                    <li>避免在索引列上使用函数</li>
                    <li>联合索引注意最左前缀原则</li>
                </ul>
                
                <h3 class="text-2xl font-semibold mb-4 flex items-center">
                    <span class="bg-blue-100 text-blue-800 rounded-full w-8 h-8 flex items-center justify-center text-sm mr-3">5.2</span>
                    事务控制
                </h3>
                <ul class="list-disc pl-6 space-y-2">
                    <li>尽量使用短事务</li>
                    <li>避免在事务中执行远程调用</li>
                    <li>合理设置事务隔离级别</li>
                </ul>
                
                <h3 class="text-2xl font-semibold mb-4 flex items-center">
                    <span class="bg-blue-100 text-blue-800 rounded-full w-8 h-8 flex items-center justify-center text-sm mr-3">5.3</span>
                    监控工具
                </h3>
                
                <div class="code-block text-gray-200 my-6">
                    <pre><code>-- 查看当前锁信息
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 监控锁等待
SHOW STATUS LIKE 'Innodb_row_lock%';</code></pre>
                </div>
            </div>
        </section>
        
        <div class="divider"></div>
        
        <!-- Section 6 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">6</span>
                我踩过的五个深坑
            </h2>
            <div class="prose max-w-none">
                <div class="grid grid-cols-1 md:grid-cols-2 gap-6 my-8">
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-red-500 text-2xl mb-3">
                            <i class="fas fa-bomb"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">UUID主键导致的性能灾难</h3>
                        <p class="text-gray-600">使用无序UUID作为主键，导致索引树频繁分裂，插入性能下降100倍</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-red-500 text-2xl mb-3">
                            <i class="fas fa-lock"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">批量更新引发的全表锁</h3>
                        <p class="text-gray-600">没有分批提交，导致千万级表被锁10分钟</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-red-500 text-2xl mb-3">
                            <i class="fas fa-exclamation-circle"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">隐式转换索引失效</h3>
                        <p class="text-gray-600"><code class="bg-gray-100 px-1 py-0.5 rounded">WHERE varchar_col = 123</code>导致索引失效，全表扫描</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="text-red-500 text-2xl mb-3">
                            <i class="fas fa-layer-group"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">不合理的事务隔离级别</h3>
                        <p class="text-gray-600">使用REPEATABLE-READ导致大量间隙锁</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg md:col-span-2">
                        <div class="text-red-500 text-2xl mb-3">
                            <i class="fas fa-tint"></i>
                        </div>
                        <h3 class="font-bold text-lg mb-2">连接池配置不当</h3>
                        <p class="text-gray-600">maxWait设置过大，导致线程堆积</p>
                    </div>
                </div>
            </div>
        </section>
        
        <div class="divider"></div>
        
        <!-- Section 7 -->
        <section class="mb-16">
            <h2 class="text-3xl font-bold mb-6 flex items-center">
                <span class="bg-blue-100 text-blue-800 rounded-full w-10 h-10 flex items-center justify-center mr-4">7</span>
                锁机制认知的三个阶段
            </h2>
            <div class="prose max-w-none">
                <div class="grid grid-cols-1 md:grid-cols-3 gap-6 my-8">
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="bg-gray-200 text-gray-800 rounded-full w-12 h-12 flex items-center justify-center text-xl font-bold mb-4 mx-auto">1</div>
                        <h3 class="font-bold text-lg mb-2 text-center">青铜阶段</h3>
                        <p class="text-gray-600 text-center">只知道表锁和行锁的区别</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="bg-gray-400 text-white rounded-full w-12 h-12 flex items-center justify-center text-xl font-bold mb-4 mx-auto">2</div>
                        <h3 class="font-bold text-lg mb-2 text-center">白银阶段</h3>
                        <p class="text-gray-600 text-center">会分析死锁日志</p>
                    </div>
                    <div class="card bg-white p-6 rounded-lg">
                        <div class="bg-yellow-500 text-white rounded-full w-12 h-12 flex items-center justify-center text-xl font-bold mb-4 mx-auto">3</div>
                        <h3 class="font-bold text-lg mb-2 text-center">王者阶段</h3>
                        <p class="text-gray-600 text-center">能预判SQL的加锁范围</p>
                    </div>
                </div>
                
                <div class="success-box my-8">
                    <div class="flex items-start">
                        <div class="flex-shrink-0 text-green-500 mt-1 mr-3">
                            <i class="fas fa-quote-left"></i>
                        </div>
                        <div>
                            <p class="font-semibold">记住：</p>
                            <p class="italic">锁不是敌人，而是守护数据一致性的卫士。真正的高手，都是能跟锁机制和谐共处的舞者。</p>
                        </div>
                    </div>
                </div>
            </div>
        </section>
    </main>
    
    <!-- Footer -->
    <footer class="bg-gray-900 text-gray-300 py-12 px-4">
        <div class="container mx-auto max-w-4xl">
            <div class="flex flex-col md:flex-row justify-between items-center">
                <div class="mb-6 md:mb-0">
                    <h3 class="text-xl font-bold text-white mb-2">技术小馆</h3>
                    <p class="text-gray-400">探索技术的无限可能</p>
                </div>
                <div>
                    <a href="http://www.yuque.com/jtostring" class="text-blue-400 hover:text-blue-300 transition-colors duration-300 flex items-center">
                        <i class="fas fa-globe mr-2"></i> http://www.yuque.com/jtostring
                    </a>
                </div>
            </div>
            <div class="mt-8 pt-8 border-t border-gray-800 text-center text-sm text-gray-500">
                &copy; 2023 技术小馆. 保留所有权利.
            </div>
        </div>
    </footer>
    
    <script>
        mermaid.initialize({
            startOnLoad: true,
            theme: 'default',
            flowchart: {
                useMaxWidth: true,
                htmlLabels: true,
                curve: 'basis'
            }
        });
    </script>
</body>
</html>